package hsit;

import cn.hutool.core.util.IdUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import sun.awt.image.ImageWatched;

import java.io.*;
import java.util.*;

/**
 * @author huanggy
 * @date 2022/07/11
 **/
public class GenerateSql4SpuProperty {


    /* 条形码 */
    public static final Map<String, String> BAR_UNIT_MAP = new HashMap<>();

    static {
        BAR_UNIT_MAP.put("盒", "cigarette_barcode_new_1");
        BAR_UNIT_MAP.put("条", "cigarette_barcode_new_2");
        BAR_UNIT_MAP.put("箱", "cigarette_barcode_new_3");
    }

    public static final Map<String, String> BAR_UNIT_ID_MAP = new HashMap<>();

    static {
        BAR_UNIT_ID_MAP.put("cigarette_barcode_new_1", "1001220523000000137");
        BAR_UNIT_ID_MAP.put("cigarette_barcode_new_2", "1001220523000000138");
        BAR_UNIT_ID_MAP.put("cigarette_barcode_new_3", "1001220523000000139");
        BAR_UNIT_ID_MAP.put("cigarette_barcode_new_4", "1001220523000000140");
    }

    /* 数量 */
    public static final Map<String, String> BAR_UNIT_COUNT_MAP = new HashMap<>();

    static {
        BAR_UNIT_COUNT_MAP.put("盒", "cigarette_pack_capacity_1");
        BAR_UNIT_COUNT_MAP.put("条", "cigarette_pack_capacity_2");
        BAR_UNIT_COUNT_MAP.put("箱", "cigarette_pack_capacity_3");
    }


    public static final Map<String, String> BAR_UNIT_COUNT_ID_MAP = new HashMap<>();

    static {
        BAR_UNIT_COUNT_ID_MAP.put("cigarette_pack_capacity_1", "1001220523000000133");
        BAR_UNIT_COUNT_ID_MAP.put("cigarette_pack_capacity_2", "1001220523000000134");
        BAR_UNIT_COUNT_ID_MAP.put("cigarette_pack_capacity_3", "1001220523000000135");
        BAR_UNIT_COUNT_ID_MAP.put("cigarette_pack_capacity_4", "1001220523000000136");
    }

    public static final String DELIMITER = "_";

    public static void main(String[] args) throws IOException {

        // 解析文件中的内容，获取产品名称、企业名称、条形码、数量
        Map<String, List<String>> productMap = new HashMap<>();
        FileReader fileReader = new FileReader("D:\\Application\\0711\\试点卷烟规格.txt");
        BufferedReader bufferedReader = new BufferedReader(fileReader);
        String line = bufferedReader.readLine();
        while (line != null) {
            line = bufferedReader.readLine();
            if (line != null) {
                String content = line.substring(1, line.length() - 1);
//                System.out.println(content);
                buildProductMap(productMap, content);
            }
        }

        // 替换ProductMap的key，改为spuId, 解析成Map<spu_name, spu_id>
        Map<String, String> spuNameAndIdMap = buildSpuNameAndIdMap();
        Map<String, String> spuIdAndBizCodeMap = buildSpuIdAndBizCodeMap();
        Map<String, List<String>> spuMap = new HashMap<>();
        productMap.keySet().forEach(key -> {
            String spuId = spuNameAndIdMap.get(key);
            if (spuId == null) {
//                System.out.println(key + "不存在");
            } else {
                spuMap.put(spuId, productMap.get(key));
            }
        });

        // 生成修改供应商ID
        productMap.keySet().forEach(spuName->{
            String spuId = spuNameAndIdMap.get(spuName);
            List<String> stringList = productMap.get(spuName);
            if(stringList != null && spuId != null){
                String[] strings = stringList.get(0).split(DELIMITER);
                String enterpriseName = strings[3];
                if(enterpriseName.startsWith("100")){
                    System.out.println("update cc_spu set provider_customer_id = '"+ enterpriseName +"' where id = '"+ spuId +"';");
                }

            }
        });


        // 生成删除
//        buildDeleteSql(spuMap);

        // 新增语句
//        buildInsertSql(spuMap, spuIdAndBizCodeMap);
    }

    public static Map<String, String> buildSpuNameAndIdMap() {
        ExcelReader reader = ExcelUtil.getReader("D:\\Application\\0711\\spu_data.xlsx");
        List<List<Object>> dataList = reader.read();
        // 移除标题
        dataList.remove(0);
        Map<String, String> productNameAndSpuIdMap = new HashMap<>();
        dataList.forEach(list -> {
            productNameAndSpuIdMap.put(list.get(1).toString(), list.get(0).toString());
        });
        return productNameAndSpuIdMap;
    }

    public static Map<String, String> buildSpuIdAndBizCodeMap() {
        ExcelReader reader = ExcelUtil.getReader("D:\\Application\\0711\\spu_data.xlsx");
        List<List<Object>> dataList = reader.read();
        // 移除标题
        dataList.remove(0);
        Map<String, String> spuIdAndBizCodeMap = new HashMap<>();
        dataList.forEach(list -> {
            spuIdAndBizCodeMap.put(list.get(0).toString(), list.get(2).toString());
        });
        return spuIdAndBizCodeMap;
    }

    public static void buildInsertSql(Map<String, List<String>> productMap, Map<String, String> spuIdAndBizCodeMap) {
        String sql = "INSERT INTO `cc_spu_property_value` (`id`,`gmt_create`,`gmt_modified`,`creator`,`modifier`,`is_deleted`,`manage_unit_id`,`property_id`,`property_value_id`,`property_biz_code`,`custom_property_value_text`,`property_type`,`subject_type`,`subject_id`,`property_value_unit`,`tenant_id`,`biz_sharding_key`,`category_id`,`spu_biz_code`) " +
                "VALUES ('%id','2022-07-11 17:31:06','2022-07-05 16:36:01','icss_dbadmin','icss_dbadmin','0','1001220126000100001','%propertyId',null,'%propertyCode','%propertyValue','PRODUCT','SPU','%subjectId',null,null,null,'1001220523000000150','%spuBizCode');";

        productMap.keySet().forEach(spuId -> {
            productMap.get(spuId).forEach(content -> {
                String[] strings = content.split(DELIMITER);
                String barUnit = strings[0];
                String unitCount = strings[1];
                String barCode = strings[2];
                String enterpriseName = strings[3];

                String id = System.currentTimeMillis() + "";
                sql.replace("%id", id);
                // 条形码
                String sql1 = sql;
                String unitBizCode = BAR_UNIT_MAP.get(barUnit);
                sql1 = sql1.replace("%id",  IdUtil.objectId())
                        .replace("%propertyCode", unitBizCode)
                        .replace("%propertyValue", barCode)
                        .replace("%propertyId", BAR_UNIT_ID_MAP.get(unitBizCode))
                        .replace("%subjectId", spuId)
                        .replace("%spuBizCode", spuIdAndBizCodeMap.get(spuId));
                System.out.println(sql1);

                // 包装数
                String sql2 = sql;
                String unitCountBizCode = BAR_UNIT_COUNT_MAP.get(barUnit);
                sql2 =  sql2.replace("%id", IdUtil.objectId())
                        .replace("%propertyCode", unitCountBizCode)
                        .replace("%propertyValue", unitCount)
                        .replace("%propertyId", BAR_UNIT_COUNT_ID_MAP.get(unitCountBizCode))
                        .replace("%subjectId", spuId)
                        .replace("%spuBizCode", spuIdAndBizCodeMap.get(spuId));
                System.out.println(sql2);
            });
        });


    }

    /**
     * 生成删除语句
     *
     * @param spuMap
     */
    public static void buildDeleteSql(Map<String, List<String>> spuMap) {
        spuMap.keySet().forEach(spuId -> {
            BAR_UNIT_MAP.keySet().forEach(key -> {
                System.out.println("delete from cc_spu_property_value where subject_id = '" + spuId + "' and property_biz_code = '" + BAR_UNIT_MAP.get(key) + "';");
            });

            BAR_UNIT_COUNT_MAP.keySet().forEach(key -> {
                System.out.println("delete from cc_spu_property_value where subject_id = '" + spuId + "' and property_biz_code = '" + BAR_UNIT_COUNT_MAP.get(key) + "';");
            });
        });
    }

    /**
     * 构建产品Map
     *
     * @param productMap
     * @param content
     */
    public static void buildProductMap(Map<String, List<String>> productMap, String content) {

        String[] contents = content.split("\\] \\[");
        String barCode = contents[1];
        String barUnit = contents[2];
        String productName = contents[3];
        String enterpriseName = contents[5];
        if (enterpriseName.contains(",")) {
            enterpriseName = enterpriseName.split(",")[0];
        }
        if("安徽中烟工业有限责任公司".equals(enterpriseName)){
            enterpriseName = "1001220126000103269";
        }else if("福建中烟工业有限责任公司".equals(enterpriseName)){
            enterpriseName = "1001220126000103278";
        }else if("江苏中烟工业有限责任公司".equals(enterpriseName)){
            enterpriseName = "1001220126000103259";
        }
        String unitCount = contents[10];


        String mapValue = String.join(DELIMITER, barUnit, unitCount, barCode, enterpriseName);
        if (productMap.containsKey(productName)) {
            List<String> strings = productMap.get(productName);
            strings.add(mapValue);
            productMap.put(productName, strings);
        } else {
            List<String> strings = new LinkedList<>();
            strings.add(mapValue);
            productMap.put(productName, strings);
        }
//        System.out.println(mapValue);
    }
}
